package com.bbcare.followup.plat.service.impl;

import java.text.DecimalFormat;
import java.util.*;

import org.apache.log4j.Logger;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.bbcare.comm.DateTimeUtil;
import com.bbcare.followup.plat.service.IStatService;


/**
 * 统计相关的功能。
 */
@Service
public class StatService implements IStatService {
    private final static Logger logger = Logger.getLogger(StatService.class);
    private final static String GY3Y_TENANT_ID  = "gzykdxfsdsyyta001";
    private final static String GWR_QUEUE_ID    = "12gzykdxfsdsyyqueue001";

    //分诊人次统计。
    /*private final static String QUERY_QUEUEUP_SQL  =
            "SELECT year, month, COUNT(*) AS total FROM (SELECT 2022 AS year, MONTH(a.seq_day) AS month, a.seq_day " +
            "FROM gy3y_queue_patient a LEFT JOIN gy3y_queue b ON a.queue_id_ex_ref=b.id " +
            "WHERE YEAR(a.seq_day)=2022 AND MONTH(a.seq_day) IN (1,2,3,4,5,6,7,8,9,10)) AS c GROUP BY c.month";*/
    //全部分诊人次数量。
    private final static String QUERY_QUEUEUP_SQL   = "SELECT COUNT(*) FROM gy3y_queue_patient";

    //全部患者数量。
    private final static String QUERY_PATIENT_SQL   = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1'";
    //高危儿队列患者数量。
    private final static String QUERY_PATIENT_SQL1  = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001'";
    //多胞胎队列患者数量。
    private final static String QUERY_PATIENT_SQL2  = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyDBTqueue001'";
    //超低/超早产儿队列。
    private final static String QUERY_PATIENT_SQL3  = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyJCqueue001'";
    //身高管理队列。
    private final static String QUERY_PATIENT_SQL4  = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue002'";
    //SGA。
    private final static String QUERY_PATIENT_SQL5  = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyySGAqueue001'";
    //神经系统疾患队列。
    private final static String QUERY_PATIENT_SQL6  = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyySJqueue001'";
    //先天性胸腹腔及胸腹腔内脏结构异常队列。
    private final static String QUERY_PATIENT_SQL7  = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyXQqueue001'";
    //支气管肺发育不良队列。
    private final static String QUERY_PATIENT_SQL8  = "SELECT COUNT(*) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyZQGqueue001'";

    //今日分诊人次。
    private final static String QUERY_TODAY_SQL1    = "SELECT COUNT(*) FROM gy3y_queue_patient WHERE seq_day=CURDATE()";
    //今日建档人数。
    private final static String QUERY_TODAY_SQL2    = "SELECT COUNT(*) FROM pt_task_info WHERE seq_no=0 AND execute_time=CURDATE()";
    //今日门诊人数（包括了建档和随访的人数，不重复计算）。
    private final static String QUERY_TODAY_SQL3    = "SELECT COUNT(DISTINCT(user_id)) FROM pt_task_info WHERE (seq_no!=0 AND finish_date=CURDATE()) OR (seq_no!=0 AND DATE(update_time)=CURDATE() AND finish_date!=CURDATE()) OR (seq_no=0 AND execute_time=CURDATE())";

    //男宝人数。
    private final static String QUERY_GENDER_SQL1   = "SELECT COUNT(*) FROM pt_user_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') AND a.gender='N'";
    //女宝人数。
    private final static String QUERY_GENDER_SQL2   = "SELECT COUNT(*) FROM pt_user_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1') AND a.gender='F'";

    //宝宝人数（全部）。
    private final static String QUERY_ALLBABY_SQL   = "SELECT COUNT(*) FROM pt_user_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    //2021年出生的宝宝人数。
    private final static String QUERY_2021BABY_SQL  = "SELECT COUNT(*) FROM pt_user_info a WHERE 2021=YEAR(a.birthday) AND a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    //2022年出生的宝宝人数。
    private final static String QUERY_2022BABY_SQL  = "SELECT COUNT(*) FROM pt_user_info a WHERE 2022=YEAR(a.birthday) AND a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    //2023年出生的宝宝人数。
    private final static String QUERY_2023BABY_SQL  = "SELECT COUNT(*) FROM pt_user_info a WHERE 2023=YEAR(a.birthday) AND a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";

    //早产儿人数（全部）。
    private final static String QUERY_PREMATURE_SQL1= "SELECT COUNT(*) FROM pt_user_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.pregnancy_day<259 AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    //2021年出生的早产儿。
    private final static String QUERY_PREMATURE2021_SQL = "SELECT COUNT(*) FROM pt_user_info a WHERE 2021=YEAR(a.birthday) AND a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.pregnancy_day<259 AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    //2022年出生的早产儿。
    private final static String QUERY_PREMATURE2022_SQL = "SELECT COUNT(*) FROM pt_user_info a WHERE 2022=YEAR(a.birthday) AND a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.pregnancy_day<259 AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";
    //2023年出生的早产儿。
    private final static String QUERY_PREMATURE2023_SQL = "SELECT COUNT(*) FROM pt_user_info a WHERE 2023=YEAR(a.birthday) AND a.tenant_id='gzykdxfsdsyyta001' AND a.user_name NOT LIKE '测%' AND a.pregnancy_day<259 AND a.id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1')";


    //全部填写了出生体重的人数。
    private final static String QUERY_WEIGHT_SQL    =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%')";
    private final static String QUERY_WEIGHT_SQL_2021   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2021=YEAR(birthday) )";
    private final static String QUERY_WEIGHT_SQL_2022   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2022=YEAR(birthday) )";
    private final static String QUERY_WEIGHT_SQL_2023   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2023=YEAR(birthday) )";

    //超低出生体重。
    private final static String QUERY_WEIGHT_SQL1   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND a.question_answer<1000 " +
            "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%')";
    private final static String QUERY_WEIGHT_SQL1_2021  =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND a.question_answer<1000 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2021=YEAR(birthday) )";
    private final static String QUERY_WEIGHT_SQL1_2022  =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND a.question_answer<1000 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2022=YEAR(birthday) )";
    private final static String QUERY_WEIGHT_SQL1_2023  =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND a.question_answer<1000 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2023=YEAR(birthday) )";


    //极低。
    private final static String QUERY_WEIGHT_SQL2   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=1000 AND CONVERT(a.question_answer, UNSIGNED)<1500 " +
            "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%')";
    private final static String QUERY_WEIGHT_SQL2_2021  =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=1000 AND CONVERT(a.question_answer, UNSIGNED)<1500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2021=YEAR(birthday)) ";
    private final static String QUERY_WEIGHT_SQL2_2022  =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=1000 AND CONVERT(a.question_answer, UNSIGNED)<1500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2022=YEAR(birthday)) ";
    private final static String QUERY_WEIGHT_SQL2_2023  =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=1000 AND CONVERT(a.question_answer, UNSIGNED)<1500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2023=YEAR(birthday)) ";

    //低。
    private final static String QUERY_WEIGHT_SQL3   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=1500 AND CONVERT(a.question_answer, UNSIGNED)<2500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%')";
    private final static String QUERY_WEIGHT_SQL3_2021   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=1500 AND CONVERT(a.question_answer, UNSIGNED)<2500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2021=YEAR(birthday))";
    private final static String QUERY_WEIGHT_SQL3_2022   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=1500 AND CONVERT(a.question_answer, UNSIGNED)<2500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2022=YEAR(birthday))";
    private final static String QUERY_WEIGHT_SQL3_2023   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=1500 AND CONVERT(a.question_answer, UNSIGNED)<2500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2023=YEAR(birthday))";


    //正常。
    private final static String QUERY_WEIGHT_SQL4   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=2500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%')";
    private final static String QUERY_WEIGHT_SQL4_2021   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=2500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2021=YEAR(birthday) )";
    private final static String QUERY_WEIGHT_SQL4_2022   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=2500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2022=YEAR(birthday) )";
    private final static String QUERY_WEIGHT_SQL4_2023   =
            "SELECT COUNT(*) FROM pt_contentdata_info a WHERE a.tenant_id='gzykdxfsdsyyta001' AND a.queue_id='12gzykdxfsdsyyqueue001' AND a.question_id='zcejd_cstz' AND a.question_answer!='' AND CONVERT(a.question_answer, UNSIGNED)>=2500 " +
                    "AND a.user_id IN (SELECT DISTINCT(user_id) FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
                    "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%' AND 2023=YEAR(birthday) )";


    //1月龄随访数量。
    private final static String QUERY_1MONTH_SQL1    =
            "SELECT COUNT(*) FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.task_name='1月龄随访'";
    //3月龄随访数量。
    private final static String QUERY_3MONTH_SQL1    =
            "SELECT COUNT(*) FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.task_name='3月龄随访'";
    //6月龄随访数量。
    private final static String QUERY_6MONTH_SQL1    =
            "SELECT COUNT(*) FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.task_name='6月龄随访'";

    //已完成的1月龄随访数量（高危儿队列）。
    private final static String QUERY_1MONTH_SQL2   =
            "SELECT COUNT(*) FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.task_name='1月龄随访' AND a.finish_date IS NOT NULL";
    //已完成的3月龄随访数量（高危儿队列）。
    private final static String QUERY_3MONTH_SQL2   =
            "SELECT COUNT(*) FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.task_name='3月龄随访' AND a.finish_date IS NOT NULL";
    //已完成的6月龄随访数量（高危儿队列）。
    private final static String QUERY_6MONTH_SQL2   =
            "SELECT COUNT(*) FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.task_name='6月龄随访' AND a.finish_date IS NOT NULL";
    //所有的临时随访。
    private final static String QUERY_TEMP_TASK =
            "SELECT * FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.temporary_task='1' AND a.finish_date IS NOT NULL";

    //未完成的1月龄随访（高危儿队列）。
    private final static String QUERY_1MONTH_SQL3   =
            "SELECT * FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.task_name='1月龄随访' AND a.finish_date IS NULL";
    //未完成的3月龄随访（高危儿队列）。
    private final static String QUERY_3MONTH_SQL3   =
            "SELECT * FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.task_name='3月龄随访' AND a.finish_date IS NULL";
    //未完成的6月龄随访（高危儿队列）。
    private final static String QUERY_6MONTH_SQL3   =
            "SELECT * FROM pt_task_info a " +
            "WHERE a.queue_id='12gzykdxfsdsyyqueue001' AND a.user_id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001') " +
            "AND a.user_id NOT IN (SELECT id FROM pt_user_info WHERE user_name LIKE '测%') AND a.task_name='6月龄随访' AND a.finish_date IS NULL";


    //排队分诊今日人次排行。
    private final static String RANK_QUEUE_SQL  =
            "SELECT b.queue_id, c.name, b.subtotal FROM (SELECT COUNT(*) AS subtotal, queue_id_ex_ref AS queue_id FROM gy3y_queue_patient a WHERE a.seq_day=CURDATE() GROUP BY queue_id_ex_ref) AS b "+
            "LEFT JOIN gy3y_queue c ON c.id=b.queue_id ORDER BY b.subtotal DESC LIMIT 5";

    //随访次数排行榜。
    private final static String RANK_FU_TASK_SQL= "SELECT user_id, COUNT(*) AS task_count FROM pt_task_info WHERE seq_no!=0 AND finish_date IS NOT NULL GROUP BY user_id ORDER BY task_count DESC";

    //查询6月龄体重百分比临界值。
    //SELECT * FROM pt_developstandard_data WHERE standard_id='00003' AND standard_type='00002' AND developProject_id='00001' AND age=180;
    //查询6月龄身长百分比临界值。
    //SELECT * FROM pt_developstandard_data WHERE standard_id='00003' AND standard_type='00002' AND developProject_id='00002' AND age=180;

    //查询6月龄体重百分比男女人数。
    private final static String MONTH6_MALE_WEIGHT_3P=
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight<6.41";
    private final static String MONTH6_FEMALE_WEIGHT_3P=
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='F' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight<5.786";
    private final static String MONTH6_MALE_WEIGHT_3_10P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=6.41 AND d.weight<6.856";
    private final static String MONTH6_FEMALE_WEIGHT_3_10P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=5.786 AND d.weight<6.218";
    private final static String MONTH6_MALE_WEIGHT_10_25P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=6.856 AND d.weight<7.334";
    private final static String MONTH6_FEMALE_WEIGHT_10_25P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=6.218 AND d.weight<6.692";
    private final static String MONTH6_MALE_WEIGHT_25_50P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=7.334 AND d.weight<7.9";
    private final static String MONTH6_FEMALE_WEIGHT_25_50P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=6.692 AND d.weight<7.265";
    private final static String MONTH6_MALE_WEIGHT_50_75P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=7.9 AND d.weight<8.504";
    private final static String MONTH6_FEMALE_WEIGHT_50_75P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=7.265 AND d.weight<7.89";
    private final static String MONTH6_MALE_WEIGHT_75_90P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=8.504 AND d.weight<9.081";
    private final static String MONTH6_FEMALE_WEIGHT_75_90P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=7.89 AND d.weight<8.503";
    private final static String MONTH6_MALE_WEIGHT_90_97P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=9.081 AND d.weight<9.684";
    private final static String MONTH6_FEMALE_WEIGHT_90_97P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=8.503 AND d.weight<9.158";
    private final static String MONTH6_MALE_WEIGHT_97P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=9.684";
    private final static String MONTH6_FEMALE_WEIGHT_97P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_tz' AND c.question_answer!='') AS d WHERE d.weight>=9.158";

    //查询6月龄身长百分比男女人数。
    private final static String MONTH6_MALE_HEIGHT_3P=
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight<63.463";
    private final static String MONTH6_FEMALE_HEIGHT_3P=
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='F' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight<61.336";
    private final static String MONTH6_MALE_HEIGHT_3_10P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=63.463 AND d.weight<64.744";
    private final static String MONTH6_FEMALE_HEIGHT_3_10P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=61.336 AND d.weight<62.692";
    private final static String MONTH6_MALE_HEIGHT_10_25P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=64.744 AND d.weight<66.042";
    private final static String MONTH6_FEMALE_HEIGHT_10_25P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=62.692 AND d.weight<64.065";
    private final static String MONTH6_MALE_HEIGHT_25_50P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=66.042 AND d.weight<67.484";
    private final static String MONTH6_FEMALE_HEIGHT_25_50P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=64.065 AND d.weight<65.591";
    private final static String MONTH6_MALE_HEIGHT_50_75P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=67.484 AND d.weight<68.926";
    private final static String MONTH6_FEMALE_HEIGHT_50_75P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=65.591 AND d.weight<67.117";
    private final static String MONTH6_MALE_HEIGHT_75_90P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=68.926 AND d.weight<70.223";
    private final static String MONTH6_FEMALE_HEIGHT_75_90P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=67.117 AND d.weight<68.49";
    private final static String MONTH6_MALE_HEIGHT_90_97P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=70.223 AND d.weight<71.505";
    private final static String MONTH6_FEMALE_HEIGHT_90_97P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=68.49 AND d.weight<69.846";
    private final static String MONTH6_MALE_HEIGHT_97P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=71.505";
    private final static String MONTH6_FEMALE_HEIGHT_97P =
            "SELECT COUNT(*) FROM (SELECT b.user_id, CONVERT(c.question_answer,DECIMAL(5,2)) AS weight FROM ( " +
            "SELECT id AS task_id, user_id FROM pt_task_info a WHERE a.task_name='6月龄随访' AND a.finish_date IS NOT NULL AND a.user_id IN (SELECT id FROM pt_user_info WHERE gender='N' AND user_name NOT LIKE '测%') " +
            ") AS b LEFT JOIN pt_contentdata_info c ON c.task_id=b.task_id WHERE c.question_id='zcey_sc' AND c.question_answer!='') AS d WHERE d.weight>=69.846";

    //分诊次数排行榜。
    private final static String QUEUEUP_TOP_LIST =
        "SELECT a.user_id, b.user_name, a.qcount, b.birthday, b.phone_no, b.gender FROM (SELECT patient_id_ex_ref AS user_id, COUNT(*) AS qcount "+
        "FROM gy3y_queue_patient GROUP BY patient_id_ex_ref) AS a LEFT JOIN pt_user_info b ON b.id=a.user_id WHERE b.tenant_id='gzykdxfsdsyyta001' ORDER BY qcount DESC";

    //高危儿队列建档总数。
    private final static String BOTTOM_ALL_CREATE   = "SELECT COUNT(*) FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND user_name NOT LIKE '测%' AND id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001')";
    //高危儿队列中XCX建档总数。
    private final static String BOTTOM_XCX_CREATE   = "SELECT COUNT(*) FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND create_author='小程序建档' AND user_name NOT LIKE '测%' AND id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001')";
    //2021年高危儿队列建档的数量
    private final static String BOTTOM_ALL_CREATE_2021  = "SELECT COUNT(*) FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND 2021=YEAR(create_time) AND user_name NOT LIKE '测%' AND id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001')";
    private final static String BOTTOM_XCX_CREATE_2021  = "SELECT COUNT(*) FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND create_author='小程序建档' AND 2021=YEAR(create_time) AND user_name NOT LIKE '测%' AND id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001')";
    private final static String BOTTOM_ALL_CREATE_2022  = "SELECT COUNT(*) FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND 2022=YEAR(create_time) AND user_name NOT LIKE '测%' AND id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001')";
    private final static String BOTTOM_XCX_CREATE_2022  = "SELECT COUNT(*) FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND create_author='小程序建档' AND 2022=YEAR(create_time) AND user_name NOT LIKE '测%' AND id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001')";
    private final static String BOTTOM_ALL_CREATE_2023  = "SELECT COUNT(*) FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND 2023=YEAR(create_time) AND user_name NOT LIKE '测%' AND id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001')";
    private final static String BOTTOM_XCX_CREATE_2023  = "SELECT COUNT(*) FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND create_author='小程序建档' AND 2023=YEAR(create_time) AND user_name NOT LIKE '测%' AND id IN (SELECT user_id FROM pt_queuescheme_info WHERE user_status='1' AND queue_id='12gzykdxfsdsyyqueue001')";

    //计算结案比。
    private final static String BOTTOM_CLOSE_CASE_UP    = "SELECT COUNT(*) FROM fu_closecase_record WHERE user_id IN (SELECT id FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND user_name NOT LIKE '测%')";
    private final static String BOTTOM_CLOSE_CASE_BELOW = "SELECT COUNT(*) FROM pt_user_info WHERE tenant_id='gzykdxfsdsyyta001' AND user_name NOT LIKE '测%'";


    @Autowired
    private JdbcTemplate jdbc;


    private static boolean hasMakeupTask(final String userId, final String schemeId, final String beginDate, final String endDate,
                                         final List<Map<String,Object>> tempTaskList)  {
        String finishDate,tempUserid,tempSchemeId;
        for(Map<String,Object> tempTaskMap : tempTaskList)   {
            tempUserid  = tempTaskMap.get("user_id").toString();
            if (!userId.equals(tempUserid)) {
                continue;
            }
            tempSchemeId= tempTaskMap.get("pt_schemeid").toString();
            if (!schemeId.equals(tempSchemeId)) {
                continue;
            }
            finishDate  = tempTaskMap.get("finish_date").toString();
            if (-1==DateTimeUtil.compareDate(beginDate, finishDate, 0) && -1==DateTimeUtil.compareDate(finishDate, endDate, 0))    {
                return true;
            }
        }
        return false;
    }

    private static Map fuRateCache  = new HashMap();
    private Map<String,Double> queryCachedFuRate(final Map<String,Object> paraMap) {
        long nowTTL = System.currentTimeMillis();
        Map retMap  = null;
        if (fuRateCache.containsKey("1月龄随访率"))  {
            retMap  = new HashMap();
            long prevTTL    = ((Long)fuRateCache.get("TTL")).longValue();
            long expire = 1000*60*60;   //1 hour.
            if (nowTTL-prevTTL>expire)  {
                //cache已超时。
                retMap  = queryFuRate(paraMap);
                fuRateCache.put("1月龄随访率",  retMap.get("1月龄随访率"));
                fuRateCache.put("3月龄随访率",  retMap.get("3月龄随访率"));
                fuRateCache.put("6月龄随访率",  retMap.get("6月龄随访率"));
                fuRateCache.put("TTL", nowTTL);
            } else {
                //cache命中。
                logger.info("++++queryCachedFuRate() hit cache.");
                retMap.put("1月龄随访率",  fuRateCache.get("1月龄随访率"));
                retMap.put("3月龄随访率",  fuRateCache.get("3月龄随访率"));
                retMap.put("6月龄随访率",  fuRateCache.get("6月龄随访率"));
            }
        } else {
            retMap  = queryFuRate(paraMap);
            fuRateCache.put("1月龄随访率",  retMap.get("1月龄随访率"));
            fuRateCache.put("3月龄随访率",  retMap.get("3月龄随访率"));
            fuRateCache.put("6月龄随访率",  retMap.get("6月龄随访率"));
            fuRateCache.put("TTL", nowTTL);
        }

        return retMap;
    }
    /**
     * 统计随访率。
     * @param paraMap
     * @return
     */
    private Map<String,Double> queryFuRate(final Map<String,Object> paraMap)    {
        long start  = System.currentTimeMillis();

        //1月龄随访数量。
        //Integer count1  = jdbc.queryForObject(QUERY_1MONTH_SQL1, Integer.class);
        //已完成的1月龄随访数量（高危儿队列）。
        //Integer count11 = jdbc.queryForObject(QUERY_1MONTH_SQL2, Integer.class);

        //3月龄随访数量。
        //Integer count3  = jdbc.queryForObject(QUERY_3MONTH_SQL1, Integer.class);
        //已完成的3月龄随访数量（高危儿队列）。
        //Integer count33 = jdbc.queryForObject(QUERY_3MONTH_SQL2, Integer.class);

        //6月龄随访数量。
        //Integer count6  = jdbc.queryForObject(QUERY_6MONTH_SQL1, Integer.class);
        //已完成的6月龄随访数量（高危儿队列）。
        //Integer count66 = jdbc.queryForObject(QUERY_6MONTH_SQL2, Integer.class);

        //全部临时随访。
        //final List<Map<String,Object>> tempTaskList   = jdbc.queryForList(QUERY_TEMP_TASK);

        //未完成的1月龄随访（高危儿队列）。
        /*
        String userId,schemeId,beginDate,endDate;
        int makeup1=0,makeup3=0,makeup6=0;
        List<Map<String,Object>> undoList   = jdbc.queryForList(QUERY_1MONTH_SQL3);
        for(Map<String,Object> undoTask : undoList)   {
            //遍历每个未完成的计划随访，查询是否有对应的临时随访。
            userId  = undoTask.get("user_id").toString();
            schemeId= undoTask.get("pt_schemeid").toString();
            beginDate   = undoTask.get("begin_time").toString();
            endDate = undoTask.get("end_time").toString();
            beginDate   = DateUtil.calcDay2(beginDate, "-", 45);
            endDate = DateUtil.calcDay2(endDate, "+", 45);
            if (hasMakeupTask(userId,schemeId,beginDate,endDate,tempTaskList))  {
                makeup1++;
            }
        }
        */
        /*
        Double rate1 = count11.doubleValue() / count1.doubleValue();
        Double rate1Makeup  = (count11.doubleValue()+makeup1) / count1.doubleValue();
        */
        Double rate1Makeup  = 0.5;
        Map<String,Double> fuRateMap    = new HashMap<>();
        fuRateMap.put("1月龄随访率", rate1Makeup);
        /*
        logger.info("++++queryRight() 1月龄随访率（未修正）："+rate1);
        logger.info("++++queryRight() 1月龄随访率（修正后）："+rate1Makeup);
        */
        /*
        undoList   = jdbc.queryForList(QUERY_3MONTH_SQL3);
        for(Map<String,Object> undoTask : undoList)   {
            //遍历每个未完成的计划随访，查询是否有对应的临时随访。
            userId  = undoTask.get("user_id").toString();
            schemeId= undoTask.get("pt_schemeid").toString();
            beginDate   = undoTask.get("begin_time").toString();
            endDate = undoTask.get("end_time").toString();
            beginDate   = DateUtil.calcDay2(beginDate, "-", 45);
            endDate = DateUtil.calcDay2(endDate, "+", 45);
            if (hasMakeupTask(userId,schemeId,beginDate,endDate,tempTaskList))  {
                makeup3++;
            }
        }
        Double rate3    = count33.doubleValue() / count3.doubleValue();
        Double rate3Makeup  = (count33.doubleValue()+makeup3) / count3.doubleValue();
        */
        Double rate3Makeup    = 0.4;
        fuRateMap.put("3月龄随访率", rate3Makeup);
        /*
        logger.info("++++queryRight() 3月龄随访率（未修正）："+rate3);
        logger.info("++++queryRight() 3月龄随访率（修正后）："+rate3Makeup);
        */
        /*
        undoList   = jdbc.queryForList(QUERY_6MONTH_SQL3);
        for(Map<String,Object> undoTask : undoList)   {
            //遍历每个未完成的计划随访，查询是否有对应的临时随访。
            userId  = undoTask.get("user_id").toString();
            schemeId= undoTask.get("pt_schemeid").toString();
            beginDate   = undoTask.get("begin_time").toString();
            endDate = undoTask.get("end_time").toString();
            beginDate   = DateUtil.calcDay2(beginDate, "-", 45);
            endDate = DateUtil.calcDay2(endDate, "+", 45);
            if (hasMakeupTask(userId,schemeId,beginDate,endDate,tempTaskList))  {
                makeup6++;
            }
        }
        Double rate6    = count66.doubleValue() / count6.doubleValue();
        Double rate6Makeup  = (count66.doubleValue()+makeup6) / count6.doubleValue();
        */
        Double rate6Makeup  = 0.3;
        fuRateMap.put("6月龄随访率", rate6Makeup);
        /*
        logger.info("++++queryRight() 6月龄随访率（未修正）："+rate6);
        logger.info("++++queryRight() 6月龄随访率（修正后）："+rate6Makeup);
        */
        return fuRateMap;
    }

    private double doubleFormat(double d)   {
        DecimalFormat format    = new DecimalFormat("#.00");
        return Double.parseDouble(format.format(d));
    }

    /**
     * 大屏右区统计结果。
     * @return
     */
    @Override
    public Map right(final Map<String,Object> paraMap) {
        Map rightMap    = new HashMap<>();  //右区。

        Map<String,Integer> taskRankMap = new HashMap<>();
        Integer count12=0,count6=0,count3=0;
        int taskCount;
        List<Map<String,Object>> taskRankList = jdbc.queryForList(RANK_FU_TASK_SQL);
        for(Map<String,Object> rankMap : taskRankList) {
            taskCount   = ((Long)rankMap.get("task_count")).intValue();
            if (taskCount>=12)  {
                count12 += taskCount;
            } else if (taskCount>=6)    {
                count6 += taskCount;
            } else if (taskCount>=3)    {
                count3 += taskCount;
            }
        }
        taskRankMap.put("随访12次以上的人数",   count12);
        taskRankMap.put("随访6~12次的人数",   count6);
        taskRankMap.put("随访3~6次的人数",    count3);
        rightMap.put("随访次数排行榜", taskRankMap);

        //下面的指标计算可以屏蔽，已提高查询速度。
        final Integer wmaleP3   = jdbc.queryForObject(MONTH6_MALE_WEIGHT_3P,  Integer.class);
        final Integer wfemaleP3 = jdbc.queryForObject(MONTH6_FEMALE_WEIGHT_3P,Integer.class);
        final Integer wmaleP3_10= jdbc.queryForObject(MONTH6_MALE_WEIGHT_3_10P,    Integer.class);
        final Integer wfemaleP3_10  = jdbc.queryForObject(MONTH6_FEMALE_WEIGHT_3_10P,  Integer.class);
        final Integer wmaleP10_25   = jdbc.queryForObject(MONTH6_MALE_WEIGHT_10_25P,Integer.class);
        final Integer wfemaleP10_25 = jdbc.queryForObject(MONTH6_FEMALE_WEIGHT_10_25P,  Integer.class);
        final Integer wmaleP25_50   = jdbc.queryForObject(MONTH6_MALE_WEIGHT_25_50P,    Integer.class);
        final Integer wfemaleP25_50 = jdbc.queryForObject(MONTH6_FEMALE_WEIGHT_25_50P,  Integer.class);
        final Integer wmaleP50_75   = jdbc.queryForObject(MONTH6_MALE_WEIGHT_50_75P,    Integer.class);
        final Integer wfemaleP50_75 = jdbc.queryForObject(MONTH6_FEMALE_WEIGHT_50_75P,  Integer.class);
        final Integer wmaleP75_90   = jdbc.queryForObject(MONTH6_MALE_WEIGHT_75_90P,    Integer.class);
        final Integer wfemaleP75_90 = jdbc.queryForObject(MONTH6_FEMALE_WEIGHT_75_90P,  Integer.class);
        final Integer wmaleP90_97   = jdbc.queryForObject(MONTH6_MALE_WEIGHT_90_97P,    Integer.class);
        final Integer wfemaleP90_97 = jdbc.queryForObject(MONTH6_FEMALE_WEIGHT_90_97P,  Integer.class);
        final Integer wmaleP97  = jdbc.queryForObject(MONTH6_MALE_WEIGHT_97P,    Integer.class);
        final Integer wfemaleP97= jdbc.queryForObject(MONTH6_FEMALE_WEIGHT_97P,  Integer.class);

        final Integer hmaleP3   = jdbc.queryForObject(MONTH6_MALE_HEIGHT_3P,  Integer.class);
        final Integer hfemaleP3 = jdbc.queryForObject(MONTH6_FEMALE_HEIGHT_3P,Integer.class);
        final Integer hmaleP3_10= jdbc.queryForObject(MONTH6_MALE_HEIGHT_3_10P,    Integer.class);
        final Integer hfemaleP3_10  = jdbc.queryForObject(MONTH6_FEMALE_HEIGHT_3_10P,  Integer.class);
        final Integer hmaleP10_25   = jdbc.queryForObject(MONTH6_MALE_HEIGHT_10_25P,Integer.class);
        final Integer hfemaleP10_25 = jdbc.queryForObject(MONTH6_FEMALE_HEIGHT_10_25P,  Integer.class);
        final Integer hmaleP25_50   = jdbc.queryForObject(MONTH6_MALE_HEIGHT_25_50P,    Integer.class);
        final Integer hfemaleP25_50 = jdbc.queryForObject(MONTH6_FEMALE_HEIGHT_25_50P,  Integer.class);
        final Integer hmaleP50_75   = jdbc.queryForObject(MONTH6_MALE_HEIGHT_50_75P,    Integer.class);
        final Integer hfemaleP50_75 = jdbc.queryForObject(MONTH6_FEMALE_HEIGHT_50_75P,  Integer.class);
        final Integer hmaleP75_90   = jdbc.queryForObject(MONTH6_MALE_HEIGHT_75_90P,    Integer.class);
        final Integer hfemaleP75_90 = jdbc.queryForObject(MONTH6_FEMALE_HEIGHT_75_90P,  Integer.class);
        final Integer hmaleP90_97   = jdbc.queryForObject(MONTH6_MALE_HEIGHT_90_97P,    Integer.class);
        final Integer hfemaleP90_97 = jdbc.queryForObject(MONTH6_FEMALE_HEIGHT_90_97P,  Integer.class);
        final Integer hmaleP97  = jdbc.queryForObject(MONTH6_MALE_HEIGHT_97P,    Integer.class);
        final Integer hfemaleP97= jdbc.queryForObject(MONTH6_FEMALE_HEIGHT_97P,  Integer.class);

        Map whMap   = new HashMap();
        whMap.put("title", "6月龄宝宝体重身长百分位人数汇总");
        List<Map> seriesList    = new ArrayList<>();
        List<Integer> wdataList  = new ArrayList<>();
        wdataList.add(wmaleP3+wfemaleP3);
        wdataList.add(wmaleP3_10+wfemaleP3_10);
        wdataList.add(wmaleP10_25+wfemaleP10_25);
        wdataList.add(wmaleP25_50+wfemaleP25_50);
        wdataList.add(wmaleP50_75+wfemaleP50_75);
        wdataList.add(wmaleP75_90+wfemaleP75_90);
        wdataList.add(wmaleP90_97+wfemaleP90_97);
        wdataList.add(wmaleP97+wfemaleP97);
        Map weightMap   = new HashMap();
        weightMap.put("name", "体重");
        weightMap.put("data", wdataList);
        seriesList.add(weightMap);

        List<Integer> hdataList  = new ArrayList<>();
        hdataList.add(hmaleP3+hfemaleP3);
        hdataList.add(hmaleP3_10+hfemaleP3_10);
        hdataList.add(hmaleP10_25+hfemaleP10_25);
        hdataList.add(hmaleP25_50+hfemaleP25_50);
        hdataList.add(hmaleP50_75+hfemaleP50_75);
        hdataList.add(hmaleP75_90+hfemaleP75_90);
        hdataList.add(hmaleP90_97+hfemaleP90_97);
        hdataList.add(hmaleP97+wfemaleP97);
        Map heightMap   = new HashMap();
        heightMap.put("name", "身长");
        heightMap.put("data", hdataList);
        seriesList.add(heightMap);
        whMap.put("series", seriesList);
        final List<String> xaxisList  = Arrays.asList("<3%","3~10%","10~25%","25~50%","50~75%","75~90%","90~97%",">97%");
        whMap.put("xAxis", xaxisList);
        rightMap.put(whMap.get("title"), whMap);

        return rightMap;
    }

    @Override
    public Map<String,Integer> center(final Map<String,Object> paraMap)    {
        Map<String,Integer> centerMap  = new HashMap<>();
        Integer count   = jdbc.queryForObject(QUERY_PATIENT_SQL1, Integer.class);
        centerMap.put("高危儿队列", count);
        centerMap.put("所有队列患者总数", count);   //根据谭主任要求，所有患者总数就等于高危儿队列的患者总数。

        count   = jdbc.queryForObject(QUERY_PATIENT_SQL2, Integer.class);
        centerMap.put("多胞胎队列", count);
        count   = jdbc.queryForObject(QUERY_PATIENT_SQL3, Integer.class);
        centerMap.put("超低超早队列", count);
        count   = jdbc.queryForObject(QUERY_PATIENT_SQL4, Integer.class);
        centerMap.put("身高管理队列", count);
        count   = jdbc.queryForObject(QUERY_PATIENT_SQL5, Integer.class);
        centerMap.put("SGA队列", count);
        count   = jdbc.queryForObject(QUERY_PATIENT_SQL6, Integer.class);
        centerMap.put("神经系统疾患队列", count);
        count   = jdbc.queryForObject(QUERY_PATIENT_SQL7, Integer.class);
        centerMap.put("先天性胸腹异常队列", count);
        count   = jdbc.queryForObject(QUERY_PATIENT_SQL8, Integer.class);
        centerMap.put("支气管肺发育不良队列", count);

        return centerMap;
    }

    @Override
    public Map left(final Map<String,Object> paraMap) {
        Map leftMap = new HashMap<>();
        Map todayMap= new HashMap<>();
        Integer count   = jdbc.queryForObject(QUERY_TODAY_SQL1, Integer.class);
        todayMap.put("今日分诊人次", count);
        count   = jdbc.queryForObject(QUERY_TODAY_SQL2, Integer.class);
        todayMap.put("今日建档人数", count);
        count   = jdbc.queryForObject(QUERY_TODAY_SQL3, Integer.class);
        todayMap.put("今日门诊人数", count);
        leftMap.put("今日诊疗人数", todayMap);

        Map rankMap = new HashMap<>();
        List<Map<String,Object>> queueRankMap   = jdbc.queryForList(RANK_QUEUE_SQL);
        for(Map<String,Object> queueRank : queueRankMap)   {
            rankMap.put(queueRank.get("name").toString(), queueRank.get("subtotal"));
        }
        leftMap.put("今日分诊排行榜", rankMap);

        leftMap.put("随访率", queryCachedFuRate(paraMap));

        return leftMap;
    }

    @Override
    public Map<String,Map<String,Integer>> bottom(final Map<String,Object> paraMap) {
        Map<String,Map<String,Integer>> bottomMap  = new HashMap<>();

        //根据出生日期统计早产比例（全部、2021年、2022、2023）。
        Integer allbaby = jdbc.queryForObject(QUERY_ALLBABY_SQL, Integer.class);    //全部宝宝数量。
        Integer allPremature   = jdbc.queryForObject(QUERY_PREMATURE_SQL1, Integer.class); //全部早产儿数量。
        Integer baby2021    = jdbc.queryForObject(QUERY_2021BABY_SQL, Integer.class);
        Integer baby2022    = jdbc.queryForObject(QUERY_2022BABY_SQL, Integer.class);
        Integer baby2023    = jdbc.queryForObject(QUERY_2023BABY_SQL, Integer.class);
        Integer premature2021   = jdbc.queryForObject(QUERY_PREMATURE2021_SQL, Integer.class);
        Integer premature2022   = jdbc.queryForObject(QUERY_PREMATURE2022_SQL, Integer.class);
        Integer premature2023   = jdbc.queryForObject(QUERY_PREMATURE2023_SQL, Integer.class);
//        Map xAxisMap    = new HashMap();
        List<String> xaxisList  = new ArrayList<String>(Arrays.asList("全部", "2021年", "2022年", "2023年"));
//        xAxisMap.put("xAxis", xaxisList);
//        Map yAxisMap    = new HashMap();
//        yAxisMap.put("type","value");
        List<Map> seriesList   = new ArrayList<>();
        Map seriesMap   = new HashMap();
        seriesList.add(seriesMap);
//        seriesMap.put("type", "bar");
        final double rate1    = allPremature.doubleValue() / allbaby.doubleValue();
        final double rate2    = premature2021.doubleValue() / baby2021.doubleValue();
        final double rate3    = premature2022.doubleValue() / baby2022.doubleValue();
        double rate4    = premature2023.doubleValue() / baby2023.doubleValue();
        if (Double.isNaN(rate4))    {
            rate4   = 0.00d;
        }
        logger.info("bottom() rate1:"+rate1+", rate2:"+rate2+", rate3:"+rate3+", rate4:"+rate4);
        List<Double> dataList   = new ArrayList<>(Arrays.asList(doubleFormat(rate1), doubleFormat(rate2), doubleFormat(rate3), doubleFormat(rate4)));
        seriesMap.put("data",   dataList);
        Map prematureMap   = new HashMap<>();
        prematureMap.put("title", "早产儿占比");
        prematureMap.put("xAxis", xaxisList);
//        prematureMap.put("yAxis", yAxisMap);
        prematureMap.put("series", seriesList);
        bottomMap.put("早产儿占比", prematureMap);


        Integer wTotal  = jdbc.queryForObject(QUERY_WEIGHT_SQL, Integer.class); //全部填写了CSTZ的人数。
        Integer wTotal21= jdbc.queryForObject(QUERY_WEIGHT_SQL_2021, Integer.class); //2021年出生且填写了CSTZ的人数。
        Integer wTotal22= jdbc.queryForObject(QUERY_WEIGHT_SQL_2022, Integer.class);
        Integer wTotal23= jdbc.queryForObject(QUERY_WEIGHT_SQL_2023, Integer.class);
        Integer w1Total = jdbc.queryForObject(QUERY_WEIGHT_SQL1, Integer.class);//填写了CSTZ且是超低体重的人数。
        Integer w2Total = jdbc.queryForObject(QUERY_WEIGHT_SQL2, Integer.class);
        Integer w3Total = jdbc.queryForObject(QUERY_WEIGHT_SQL3, Integer.class);
        Integer w4Total = jdbc.queryForObject(QUERY_WEIGHT_SQL4, Integer.class);

        Integer w1Total21   = jdbc.queryForObject(QUERY_WEIGHT_SQL1_2021, Integer.class);//2021年出生超低体重的人数。
        Integer w2Total21   = jdbc.queryForObject(QUERY_WEIGHT_SQL2_2021, Integer.class);//2021年出生极低体重的人数。
        Integer w3Total21   = jdbc.queryForObject(QUERY_WEIGHT_SQL3_2021, Integer.class);//2021年出生低体重的人数。
        Integer w4Total21   = jdbc.queryForObject(QUERY_WEIGHT_SQL4_2021, Integer.class);//2021年出生正常体重的人数。

        Integer w1Total22   = jdbc.queryForObject(QUERY_WEIGHT_SQL1_2022, Integer.class);//2022年出生超低体重的人数。
        Integer w2Total22   = jdbc.queryForObject(QUERY_WEIGHT_SQL2_2022, Integer.class);//2022年出生极低体重的人数。
        Integer w3Total22   = jdbc.queryForObject(QUERY_WEIGHT_SQL3_2022, Integer.class);//2022年出生低体重的人数。
        Integer w4Total22   = jdbc.queryForObject(QUERY_WEIGHT_SQL4_2022, Integer.class);//2022年出生正常体重的人数。

        Integer w1Total23   = jdbc.queryForObject(QUERY_WEIGHT_SQL1_2023, Integer.class);//2023年出生超低体重的人数。
        Integer w2Total23   = jdbc.queryForObject(QUERY_WEIGHT_SQL2_2023, Integer.class);//2023年出生极低体重的人数。
        Integer w3Total23   = jdbc.queryForObject(QUERY_WEIGHT_SQL3_2023, Integer.class);//2023年出生低体重的人数。
        Integer w4Total23   = jdbc.queryForObject(QUERY_WEIGHT_SQL4_2023, Integer.class);//2023年出生正常体重的人数。
        Double s1Rate1  = w1Total.doubleValue()   / wTotal.doubleValue();     //全部   超低体重宝宝的比例。
        Double s2Rate1  = w1Total21.doubleValue() / wTotal21.doubleValue(); //2021年 超低体重宝宝的比例。
        Double s3Rate1  = w1Total22.doubleValue() / wTotal22.doubleValue(); //2022年 超低体重宝宝的比例。
        Double s4Rate1  = w1Total23.doubleValue() / wTotal23.doubleValue(); //2022年 超低体重宝宝的比例。
        Double s1Rate2  = w2Total.doubleValue()   / wTotal.doubleValue();     //全部   极低体重宝宝的比例。
        Double s2Rate2  = w2Total21.doubleValue() / wTotal21.doubleValue(); //2021年 极低体重宝宝的比例。
        Double s3Rate2  = w2Total22.doubleValue() / wTotal22.doubleValue(); //2022年 极低体重宝宝的比例。
        Double s4Rate2  = w2Total23.doubleValue() / wTotal23.doubleValue(); //2023年 极低体重宝宝的比例。
        Double s1Rate3  = w3Total.doubleValue()   / wTotal.doubleValue();   //全部   低体重宝宝的比例。
        Double s2Rate3  = w3Total21.doubleValue() / wTotal21.doubleValue(); //2021年 低体重宝宝的比例。
        Double s3Rate3  = w3Total22.doubleValue() / wTotal22.doubleValue(); //2022年 低体重宝宝的比例。
        Double s4Rate3  = w3Total23.doubleValue() / wTotal23.doubleValue(); //2023年 低体重宝宝的比例。
        Double s1Rate4  = w4Total.doubleValue()   / wTotal.doubleValue();   //全部   正常体重宝宝的比例。
        Double s2Rate4  = w4Total21.doubleValue() / wTotal21.doubleValue(); //2021年 正常体重宝宝的比例。
        Double s3Rate4  = w4Total22.doubleValue() / wTotal22.doubleValue(); //2022年 正常体重宝宝的比例。
        Double s4Rate4  = w4Total23.doubleValue() / wTotal23.doubleValue(); //2023年 正常体重宝宝的比例。

        List<Double> g1Data = new ArrayList<>(Arrays.asList(doubleFormat(s1Rate1), doubleFormat(s2Rate1), doubleFormat(s3Rate1), doubleFormat(s4Rate1)));
        List<Double> g2Data = new ArrayList<>(Arrays.asList(doubleFormat(s1Rate2), doubleFormat(s2Rate2), doubleFormat(s3Rate2), doubleFormat(s4Rate2)));
        List<Double> g3Data = new ArrayList<>(Arrays.asList(doubleFormat(s1Rate3), doubleFormat(s2Rate3), doubleFormat(s3Rate3), doubleFormat(s4Rate3)));
        List<Double> g4Data = new ArrayList<>(Arrays.asList(doubleFormat(s1Rate4), doubleFormat(s2Rate4), doubleFormat(s3Rate4), doubleFormat(s4Rate4)));
        Map weightMap   = new HashMap<>();
        weightMap.put("title", "出生体重分级汇总");
        final List<String> wAxisList  = Arrays.asList("全部","2021年","2022年","2023年");
        weightMap.put("xAxis", wAxisList);
        List<Map> wSeriesList   = new ArrayList<>();
        Map g1Map = new HashMap();
        g1Map.put("name", "超低体重");
        g1Map.put("data", g1Data);
        wSeriesList.add(g1Map);
        Map g2Map = new HashMap();
        g2Map.put("name", "极低体重");
        g2Map.put("data", g2Data);
        wSeriesList.add(g2Map);
        Map g3Map = new HashMap();
        g3Map.put("name", "低体重");
        g3Map.put("data", g3Data);
        wSeriesList.add(g3Map);
        Map g4Map = new HashMap();
        g4Map.put("name", "正常体重");
        g4Map.put("data", g4Data);
        wSeriesList.add(g4Map);
        weightMap.put("series", wSeriesList);
        bottomMap.put("出生体重分类占比", weightMap);


        //统计高危儿队列的XCX建档比例（全部、2021年、2022、2023）。
        Integer jdAll   = jdbc.queryForObject(BOTTOM_ALL_CREATE, Integer.class);    //高危儿队列的建档数量。
        Integer jdXcx   = jdbc.queryForObject(BOTTOM_XCX_CREATE, Integer.class);    //高危儿队列XCX的建档数量。
        Integer jd2021  = jdbc.queryForObject(BOTTOM_ALL_CREATE_2021, Integer.class);
        Integer jdXcx2021   = jdbc.queryForObject(BOTTOM_XCX_CREATE_2021, Integer.class);
        Integer jd2022  = jdbc.queryForObject(BOTTOM_ALL_CREATE_2022, Integer.class);
        Integer jdXcx2022   = jdbc.queryForObject(BOTTOM_XCX_CREATE_2022, Integer.class);
        Integer jd2023  = jdbc.queryForObject(BOTTOM_ALL_CREATE_2023, Integer.class);
        Integer jdXcx2023   = jdbc.queryForObject(BOTTOM_XCX_CREATE_2023, Integer.class);
//        Map jdXaxisMap    = new HashMap();
//        jdXaxisMap.put("type","category");
        List<String> jdXaxisList  = new ArrayList<String>(Arrays.asList("全部", "2021年", "2022年", "2023年"));
//        jdXaxisMap.put("xAxis", jdXaxisList);
//        Map jdYaxisMap    = new HashMap();
//        jdYaxisMap.put("type","value");
        List<Map> jdSeriesList   = new ArrayList<>();
        Map jdSeriesMap   = new HashMap();
        jdSeriesList.add(jdSeriesMap);
//        jdSeriesMap.put("type", "bar");
        final double jdRateAll  = jdXcx.doubleValue()     / jdAll.doubleValue();
        final double jdRate2021 = jdXcx2021.doubleValue() / jd2021.doubleValue();
        final double jdRate2022 = jdXcx2022.doubleValue() / jd2022.doubleValue();
        double jdRate2023 = jdXcx2023.doubleValue() / jd2023.doubleValue();
        if (Double.isNaN(jdRate2023))   {
            jdRate2023  = 0.00d;
        }
        List<Double> jdDataList   = new ArrayList<>(Arrays.asList(doubleFormat(jdRateAll), doubleFormat(jdRate2021), doubleFormat(jdRate2022), doubleFormat(jdRate2023)));
        jdSeriesMap.put("data",   jdDataList);
        Map jdMap   = new HashMap<>();
        jdMap.put("title", "小程序建档占比");
        jdMap.put("xAxis", jdXaxisList);
//        jdMap.put("yAxis", jdYaxisMap);
        jdMap.put("series", jdSeriesList);
        bottomMap.put("小程序建档占比", jdMap);


        Map closecaseMap   = new HashMap<>();
        final Integer closeUp   = jdbc.queryForObject(BOTTOM_CLOSE_CASE_UP, Integer.class);
        final Integer closeBelow= jdbc.queryForObject(BOTTOM_CLOSE_CASE_BELOW, Integer.class);
        final int iCloseRate    = (int)((float)closeUp / (float)closeBelow * 100.0f);
        closecaseMap.put("title","结案比");
        closecaseMap.put("type", "gauge");
        closecaseMap.put("value",   iCloseRate);
        bottomMap.put("结案比", closecaseMap);

        return bottomMap;
    }

}
